﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Koonsoft.HYCRM.Model;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace Koonsoft.HYCRM.DAL
{
    /// <summary>
    /// 访问公司对象的数据层
    /// </summary>
    public class CompanyDAL
    {
        private static readonly Database Database = DatabaseFactory.CreateDatabase("KOONCRMDB");

        /// <summary>
        /// 根据SerialNumber获取公司对象
        /// </summary>
        /// <param name="serialNumber">公司序号</param>
        /// <returns></returns>
        public Company GetCompanyBySerialNumber(string serialNumber)
        {
            const string sql = @"select * from company where CompanySerialNumber=@CompanySerialNumber";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanySerialNumber", DbType.String, serialNumber);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return new Company()
                    {
                        CompanyID = sqlrd["CompanyID"].ToString(),
                        ChineseName = sqlrd["ChineseName"].ToString(),
                        EnglishName = sqlrd["EnglishName"].ToString(),
                        RegionID = sqlrd["RegionID"].ToString()
                    };
                }

                return null;
            }
        }

        /// <summary>
        /// 根据companyId获取公司对象
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public Company GetCompanyByCompanyid(string companyId)
        {
            const string sql = @"select * from company where Companyid=@companyid";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "companyid", DbType.String, companyId);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    var v = new Company()
                    {
                        CompanyID = sqlrd["CompanyID"].ToString(),
                        ChineseName = sqlrd["ChineseName"].ToString(),
                        EnglishName = sqlrd["EnglishName"].ToString(),
                        RegionID = sqlrd["RegionID"].ToString(),
                        Chairman = sqlrd["Chairman"].ToString(),
                        Stock = Convert.ToInt32(sqlrd["Stock"].ToString()),
                        HasProfitTax = Convert.ToBoolean(sqlrd["HasProfitTax"].ToString())
                    };

                    v.RegisterDate = Convert.ToDateTime(sqlrd["RegisterDate"].ToString());

                    return v;
                }

                return null;
            }
        }

        /// <summary>
        /// 获取分页的公司数据
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="recordCount"></param>
        /// <param name="orderStart"></param>
        /// <returns></returns>
        public DataTable GetCompaniesByPager(string orderStart, string orderEnd, string englishName, string chineseName,
                            string companyid, int regionID, int pageSize, int pageIndex, out int recordCount)
        {
            recordCount = 0;

            const string procedure = "Company_GetCompanies";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                if (!string.IsNullOrEmpty(orderStart))
                {
                    Database.AddInParameter(cmd, "@orderstart", DbType.DateTime, Convert.ToDateTime(orderStart));
                }
                if (!string.IsNullOrEmpty(orderEnd))
                {
                    Database.AddInParameter(cmd, "@orderend", DbType.DateTime, Convert.ToDateTime(orderEnd));
                }

                Database.AddInParameter(cmd, "@englishname", DbType.String, englishName);
                Database.AddInParameter(cmd, "@chinesename", DbType.String, chineseName);
                Database.AddInParameter(cmd, "@companyid", DbType.String, companyid);
                Database.AddInParameter(cmd, "@regionid", DbType.Int32, regionID);

                Database.AddInParameter(cmd, "@pagesize", DbType.Int32, pageSize);
                Database.AddInParameter(cmd, "@pageindex", DbType.Int32, pageIndex);
                Database.AddOutParameter(cmd, "@recordcount", DbType.Int32, recordCount);

                DataSet ds = Database.ExecuteDataSet(cmd);

                recordCount = Convert.ToInt32(cmd.Parameters["@recordcount"].Value);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取分页的公司数据(快递)
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="recordCount"></param>
        /// <param name="orderStart"></param>
        /// <returns></returns>
        public DataTable GetCompaniesByPagerByKuaiDi(string englishName, string chineseName,
                            string companyid, int regionID, int pageSize, int pageIndex, out int recordCount)
        {
            recordCount = 0;

            const string procedure = "Company_GetCompanies_ByKuaiDi";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@englishname", DbType.String, englishName);
                Database.AddInParameter(cmd, "@chinesename", DbType.String, chineseName);
                Database.AddInParameter(cmd, "@companyid", DbType.String, companyid);
                Database.AddInParameter(cmd, "@regionid", DbType.Int32, regionID);

                Database.AddInParameter(cmd, "@pagesize", DbType.Int32, pageSize);
                Database.AddInParameter(cmd, "@pageindex", DbType.Int32, pageIndex);
                Database.AddOutParameter(cmd, "@recordcount", DbType.Int32, recordCount);

                DataSet ds = Database.ExecuteDataSet(cmd);

                recordCount = Convert.ToInt32(cmd.Parameters["@recordcount"].Value);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 根据公司编号获取公司信息
        /// </summary>
        /// <param name="companyID"></param>
        /// <param name="regionID"></param>
        /// <returns></returns>
        public CompanyInfo GetCompanyInfoByCompanyIDAndRegionID(string companyID, string regionID)
        {
            const string sql = @"select * from company where companyID=@companyid and regionID=@regionid";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@companyid", DbType.String, companyID);
                Database.AddInParameter(cmd, "@regionid", DbType.String, regionID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return new CompanyInfo
                    {
                        EnglishName = sqlrd["EnglishName"].ToString(),
                        ChineseName = sqlrd["ChineseName"].ToString(),
                        RegisterDate = Convert.ToDateTime(sqlrd["RegisterDate"]),
                        Stock = Convert.ToInt32(sqlrd["stock"]),
                        RegisterRegionID = Convert.ToInt32(sqlrd["regionid"])
                    };
                }
            }

            return null;
        }

        /// <summary>
        /// 删除公司（需要同时删除相关的客人和订单才对，但目前未实现）
        /// </summary>
        /// <param name="companySerialNumber"></param>
        public void Remove(string companySerialNumber)
        {
            const string procedure = @"Company_Remove";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@companyserialnumber", DbType.String, companySerialNumber);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 根据订单编号获取公司信息
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public CompanyInfo GetCompanyInfoByOrderID(string orderID)
        {
            const string procedure = "Company_GetCompanyInfoByOrderID";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@OrderID", DbType.String, orderID);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    return new CompanyInfo
                               {
                                   EnglishName = sqlrd["EnglishName"].ToString(),
                                   ChineseName = sqlrd["ChineseName"].ToString(),
                                   RegisterDate = Convert.ToDateTime(sqlrd["RegisterDate"]),
                                   Stock = Convert.ToInt32(sqlrd["stock"]),
                                   RegisterRegionID = Convert.ToInt32(sqlrd["regionid"]),
                                   CompanyID = sqlrd["CompanyID"].ToString(),
                                   SerialNumber = sqlrd["CompanySerialNumber"].ToString()
                               };
                }
            }

            return null;
        }

        /// <summary>
        /// 文件处理时更新公司资料
        /// </summary>
        /// <param name="companyID"></param>
        /// <param name="englishName"></param>
        /// <param name="chineseName"></param>
        /// <param name="serialNumber"></param>
        public void FileHandleByCompanySerialNumber(string companyID, string englishName, string chineseName, string serialNumber, string registerDate)
        {
            const string procedure = "Company_FileHandle";

            using (var cmd = Database.GetStoredProcCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyID", DbType.String, companyID);
                Database.AddInParameter(cmd, "@EnglishName", DbType.String, englishName);
                Database.AddInParameter(cmd, "@ChineseName", DbType.String, chineseName);
                Database.AddInParameter(cmd, "@CompanySerialNumber", DbType.String, serialNumber);
                Database.AddInParameter(cmd, "@registerDate", DbType.DateTime, Convert.ToDateTime(registerDate));

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 变更公司状态
        /// </summary>
        /// <param name="companySerialNumber"></param>
        /// <param name="companyStatus"></param>
        public void UpdateCompanyStatus(string companySerialNumber, int companyStatus)
        {
            const string sql = "update company set [status]=@status where companyserialnumber=@companyserialnumber";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@status", DbType.Int32, companyStatus);
                Database.AddInParameter(cmd, "@companyserialnumber", DbType.String, companySerialNumber);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 变更公司注册日期
        /// </summary>
        /// <param name="registerDate"></param>
        /// <param name="companySerialNumber"></param>
        public void UpdateRegisterDate(string registerDate, string companySerialNumber)
        {
            const string sql = "update company set registerdate=@registerdate where companyserialnumber=@companyserialnumber";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@registerdate", DbType.DateTime, Convert.ToDateTime(registerDate));
                Database.AddInParameter(cmd, "@companyserialnumber", DbType.String, companySerialNumber);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 新增董事/股东信息
        /// </summary>
        /// <param name="companyId"></param>
        /// <param name="directorName"></param>
        /// <param name="proportion"></param>
        /// <param name="ISDirector">0:股东  1:董事</param>
        public void CreateCompanyDiectors(string companyId, string directorName,string EnglishName, double proportion, string ISDirector)
        {
            const string sql = @"
                      insert into [CompanyDirectors](
		                            [CompanyId]
                                  ,[DirectorName]
                                  ,[Proportion]
                                  ,[ISDirector],EnglishName)
                               values(@CompanyId,@DirectorName,@Proportion,@ISDirector,@EnglishName)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyId);
                Database.AddInParameter(cmd, "@DirectorName", DbType.String, directorName);
                Database.AddInParameter(cmd, "@Proportion", DbType.Double, proportion);
                Database.AddInParameter(cmd, "@ISDirector", DbType.Double, ISDirector);
                Database.AddInParameter(cmd, "@EnglishName", DbType.String, EnglishName);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 清空公司董事信息
        /// </summary>
        /// <param name="companyId"></param>
        public void ClearCompanyDiectors(string companyId)
        {
            const string sql = @"delete [CompanyDirectors] where CompanyId =@CompanyId";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyId);
                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 根据公司ID获取董事信息
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public List<CompanyDiectorsEntity> GetCompanyDiectors(string companyId)
        {
            List<CompanyDiectorsEntity> list = new List<CompanyDiectorsEntity>();
            const string procedure = "select * from [CompanyDirectors] where CompanyId =@CompanyId";

            using (var cmd = Database.GetSqlStringCommand(procedure))
            {
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, companyId);

                //DataSet ds = Database.ExecuteDataSet(cmd);
                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);
                var sqlrd = (SqlDataReader)rd.InnerReader;

                while (sqlrd.Read())
                {
                    var v = new CompanyDiectorsEntity()
                    {
                        DirectorName = sqlrd["DirectorName"].ToString(),
                        ISDirector = Convert.ToInt32(sqlrd["ISDirector"].ToString()),
                        Proportion = Convert.ToDouble(sqlrd["Proportion"].ToString()),
                        EnglishName = sqlrd["EnglishName"].ToString()
                    };
                    list.Add(v);

                }


                return list;
            }
        }



        /// <summary>
        /// 根据OrderId获取核数内容
        /// </summary>
        /// <param name="companyId"></param>
        /// <returns></returns>
        public CompanyAuditorsEntity GetCompanyAuditorsEntityByOrderId(string OrderId)
        {
            const string sql = @"select * from CompanyAuditors where OrderId=@OrderId";

            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "OrderId", DbType.String, OrderId);

                var rd = (RefCountingDataReader)Database.ExecuteReader(cmd);

                var sqlrd = (SqlDataReader)rd.InnerReader;

                if (sqlrd.HasRows)
                {
                    sqlrd.Read();
                    var v = new CompanyAuditorsEntity()
                    {
                        CompanyId = sqlrd["CompanyId"].ToString(),
                        OrderId = sqlrd["OrderId"].ToString(),
                        BR = sqlrd["BR"].ToString(),
                        Id = Convert.ToInt32(sqlrd["Id"]),
                        BusinessScope = sqlrd["BusinessScope"].ToString(),
                        BusinessAddress = sqlrd["BusinessAddress"].ToString(),
                        TotalTurnover =  Convert.ToInt32(sqlrd["TotalTurnover"].ToString()),
                        Status = Convert.ToInt32(sqlrd["status"].ToString())
                        
                    };

                    v.StartTime = Convert.ToDateTime(sqlrd["StartTime"].ToString());
                    v.EndTime = Convert.ToDateTime(sqlrd["EndTime"].ToString());

                    return v;
                }

                return null;
            }
        }



        /// <summary>
        /// 核数基本信息
        /// </summary>
        public void CreateCompanyAuditors(CompanyAuditorsEntity entity)
        {
            const string sql = @"
                        delete CompanyAuditors where @OrderId = OrderId;
                      insert into [CompanyAuditors](
		                           [BR]
                                  ,[StartTime]
                                  ,[EndTime]
                                  ,[OrderId]
                                  ,[CompanyId]
                                    ,[BusinessScope]
                                    ,[BusinessAddress]
                                    ,[TotalTurnover])
                               values(@BR,@StartTime,@EndTime,@OrderId,@CompanyId,@BusinessScope,@BusinessAddress,@TotalTurnover)";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@BR", DbType.String, entity.BR);
                Database.AddInParameter(cmd, "@StartTime", DbType.DateTime, entity.StartTime);
                Database.AddInParameter(cmd, "@EndTime", DbType.DateTime, entity.EndTime);
                Database.AddInParameter(cmd, "@OrderId", DbType.String, entity.OrderId);
                Database.AddInParameter(cmd, "@CompanyId", DbType.String, entity.CompanyId);
                Database.AddInParameter(cmd, "@BusinessScope", DbType.String, entity.BusinessScope);
                Database.AddInParameter(cmd, "@BusinessAddress", DbType.String, entity.BusinessAddress);
                Database.AddInParameter(cmd, "@TotalTurnover", DbType.Int32, entity.TotalTurnover);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 核数基本信息
        /// </summary>
        public void UpdateCompanyAuditorsStatuc(string OrderId, int Status, string loginName)
        {
            const string sql = @"Auditor_UpdateCompanyAuditorsStatuc";


            using (var cmd = Database.GetStoredProcCommand(sql))
            {
                Database.AddInParameter(cmd, "@OrderId", DbType.String, OrderId);
                Database.AddInParameter(cmd, "@Status", DbType.Int32, Status);
                Database.AddInParameter(cmd, "@loginName", DbType.String, loginName);

                Database.ExecuteNonQuery(cmd);
            }
        }

        /// <summary>
        /// 查询核数状态变更日志
        /// </summary>
        public DataTable CompanyAuditorsStatusLog(string OrderId)
        {
            const string sql = @"select orderid,UpdateTime,Status,loginName  From CompanyAuditorsStatusUpdateLog
                                where orderid = @orderid
                                union

                                select orderId,orderTime as UpdateTime , 0 as Status,b.loginName From dbo.[Order]  a
                                join empolyee b on a.empolyeeid = b.empolyeeid
                                where orderid = @orderid

                                order by status 
                                ";


            using (var cmd = Database.GetSqlStringCommand(sql))
            {
                Database.AddInParameter(cmd, "@OrderId", DbType.String, OrderId);

                DataSet ds = Database.ExecuteDataSet(cmd);
                
                return ds.Tables[0];
            }
        }
    }
}
